<?php


/**
* This file was created in 04/02/2008.
* It is used to __toString SQL's using object orientation.
* It only be tested on MySql query.
*
* It is an alpha test, please use carefully.
*
* Create by Eduardo "Trialforce" Bonfandini
* trialforce@hotmail.com
* trialforce@gmail.com
* trialforce#yahoo.com.br
* eduardo@nostaljia.com
*
*
*/


define('SELECT'  , 'select'     );
define('FROM'    , 'from'       );
define('CREATE'  , 'Create'     );
define('TABLE'   , 'table'      );
define('ANDD'    , 'and'        );
define('OR'      , 'or'         );
define('COMMA'   , ' , '        );
define('WHERE'   , 'where'      );
define('UPDATE'  , 'update'     );
define('SET'     , 'set'        );
define('INSERT'  , 'insert into');
define('ORDERBY' , 'order by'   );
define('LIMIT'   , 'limit');


// TODO where between
// TODO groupby
// TODO having
// TODO limit
// TODO updateDb, insertDb, deleteDb
// TODO toString() to __toString()
// LEFT JOIN... heeh realy need ?? hehe

/**
* COLUMNS
* Manage a colum of a table, in sql.
* It stores the Name and Type of the column.
*
*
*/
class column
{
    public $name ='';
    public $type ='';


    /**
    *  Create a column
    *  @param $name the name of the colum
    *  @param $type the type of the colum (Ex. integer, varchar)
    *
    */
    function column($name, $type=null)
    {
        $this->name = $name;
        $this->type = $type;
    }


    /**
    * Define the name of colum
    *
    * @param $name the name of the column.
    */
    function setName($name)
    {
        $this->name = $name;
    }


    /**
    * Return the name of the colum
    *
    * @return the name of the colum
    */
    function getName()
    {
        return $this->name;
    }


    /**
    * Define the Type of the Column.
    *
    * @param $type the type of colum
    */
    function setType($type)
    {
        $this->type = $type;
    }


    /**
    * Return the type of this column.
    *
    * @return the type of this column.
    */
    function getType()
    {
        return $this->type;
    }


    /**
    * Convert a Column to string. Only to show it information;
    *
    *
    */
    function toString()
    {
        return $this->name . ' - ' . $this->type;
    }

}





/**
* TABLE
* Class to create Tables.
*
*
*/
class table
{
    public $name        = '';
    public $alias       = '';
    public $columns     = null;
    public $limitBegin  = null;
    public $limitEnd    = null;
    public $where       = null;
    public $orderBy     = null;
    public $db          = NULL;

    /**
    * Constructor of Table Class.
    * /TODO pass columns in constructor
    *
    * @param $name the name of the table
    * @param $alias the alias of table
    * @param $db the database object
    *
    */
    function table($name, $alias = NULL, $db = NULL)
    {
    	if (!$db)
    	{
    	   global $db;
    	}
        $this->name  = $name;
        $this->alias = $alias;
        $this->db    = $db;
    }

    /**
    * Return a select sql.
    *
    * @param $string extra param to add to select
    * @param $columns the columns of this select, if you not pass it assume the table columns, and if the table dont has columns it assumes "*" all columns.
    */
    function select($param = '', $columns=null)
    {
        if ($this->name)
        {

            $result .= SELECT . ' ';

            //param
            if ($param != '')
            {
                $result.=$param. ' ';
            }

            //if user pass $column
            if ($columns)
            {
                if (is_array($columns))
                {
                    foreach ($columns as $line => $info)
                    {
                        if ($line == 0)
                        {
                            $result .= $info;
                        }
                        else
                        {
                            $result .= COMMA . $info;
                        }
                    }
                }
                else
                {
                    $result.= $columns;
                }
                $result .= " " .FROM . " ";
            }
            else
            //if user do not pass $columns and has columns in table
            if ($this->columns)
            {
                    $columns = $this->getColumns();

                    foreach ($columns as $line => $info)
                    {
                        if ($line==0)
                        {
                            $result .= $columns[$line]->getName();
                        }
                        else
                        {
                            $result .= ' , ' . $columns[$line]->getName();
                        }
                    }

                $result .=  " " .FROM . " ";

            }
            //table dont has columns and user dont pass it
            else
            {
                $result .= SELECT . ' * ' . FROM  . ' ' ;
            }

            //table name and alias

            if ($this->alias)
            {
                $result  .= $this->name . ' ' .  $this->alias;
            }
            else
            {
                $result .= $this->name;
            }

            //get where, the end ';' and return it
            return $result.$this->getWhereToSql().' '.$this->getOrderByToSql().' '. $this->getLimitToSqL().';';
        }
        else
        {
            return false;
        }

    }


    /**
    * Return a Create table sql;
    *
    */
    function create()
    {
    	$result = '';

        if ($this->name)
        {
            $result.= CREATE .' ' .  TABLE .' '. $this->name . ' (';

            if ($this->columns)
            {
                $columns = $this->getColumns();

                foreach ($columns as $line => $info)
                {
                    if ($line==0)
                    {
                        $result .= $columns[$line]->getName(). ' ' . $columns[$line]->getType();
                    }
                    else
                    {
                        $result .= COMMA . $columns[$line]->getName(). ' ' . $columns[$line]->getType();
                    }
                }
                $result.= ');';

                return $result;
            }
            else
            {
                return false;
            }
        }
        else
        {
            return false;
        }
    }


    public function drop()
    {
        //todo
    }


    /**
    * Create a update SQL.
    * You must it is this way:
    * Pass an array within fields, the array must be
    * fieldName = 'value';
    * if FieldName encountered in fields list the data will be update, else do not.
    * it uses the Where statement created in table.
    *
    */
    public function update($values)
    {
        if ($this->name)
        {
            $result = UPDATE . ' ' .$this->name . ' ' . SET .' ' ;
            $result.=$this->getValues($values).$this->getWhereToSql().';';
            return $result;
          }
        else
        {
            return false;
        }
    }


    public function insert($values)
    {
        if ($this->name)
        {
            $result  = INSERT . ' ' .$this->name . ' (' . $this->getQueryColumns($values, true) .') values ('.$this->getQueryColumns($values, false) .');';
            return $result;
        }
        else
        {
            return false;
        }
    }


    private function getValues($values)
    {
        if ( !$values )
        {
            return false;
        }

    	$columns = $this->columns;
    	$first=true;

        if ( is_array( $values ) )
        {
            foreach ($values as $line => $info)
            {
            	//verifies if $values is in columlist to avoid errors...
			    $can = false;
            	foreach ( $columns as $l => $i)
            	{
				    if ($i->name == $line)
				    {
					    $can = true;
				    }
            	}

			    if ($can)
			    {
		            if ($first == true)
	                {
	                    $result.= " $line = '$info'";
	                    $first = false;
	                }
	                else
	                {
	                    $result.= " , $line = '$info'";
	                }
			    }
            }
        }

        return $result;
    }


    /**
    * Return the columns available to this query analising this table
    *
    */
    private function getQueryColumns($values, $returnType=FALSE)
    {
    	$columns = $this->columns;
    	$first=true;

        foreach ($values as $line => $info)
        {
        	//verifies if $values is in columlist to avoid errors...
			$can = false;
        	foreach ( $columns as $l => $i)
        	{
				if ($i->name == $line)
				{
					$can = true;
				}
        	}

			if ($can)
			{
                if ($returnType ==true)
                {
		            if ($first == true)
	                {
	                    $result.= " $line";
	                    $first = false;
	                }
	                else
	                {
	                    $result.= " , $line";
	                }
                }
                else
                {
		            if ($first == true)
	                {
	                    $result.= " '$info'";
	                    $first = false;
	                }
	                else
	                {
	                    $result.= " , '$info'";
	                }
                }
			}
        }

        return $result;
    }

    public function delete()
    {
        //delete from login where login = 'trialforce';
        if ($this->name)
        {
            $result = DELETE . ' ' . FROM  . ' ' . $this->name .$this->getWhereToSql() . ';'  ;
            return $result;
        }
        else
        {
            return false;
        }
    }


    /************** DEFINITIONS **************/

    function clear()
    {
        $this->clearWhere();
        $this->clearLimit();
        $this->clearOrderBy();
    }

    /**
    * Return the name of this table.
    *
    */
    function getName()
    {
        return $this->name;
    }


    /**
    * Change/Define the Name of this table.
    *
    */
    function setName($name)
    {
        $this->name = $name;
    }


    /**
    * Return the alias of this table.
    *
    */
    function getAlias()
    {
        return $this->alias;
    }


    /**
    * Change/Define the alias of this table.
    *
    */
    function setAlias($alias)
    {
        $this->alias = $alias;
    }


    /************** COLUMNS **************/

    /**
    * Add a column to table.
    *
    * @param $name the name of column
    * @param $type the type of column
    */
    function addColumn($name, $type=null)
    {
        $this->columns[] = new Column($name, $type);
    }


    /**
    * Add an array of column.
    *
    * @param $columns an array of strings with the name of column.
    */
    function addColumns($columns)
    {
        if (is_array($columns))
        {
            foreach ($columns as $line => $info)
            {
                $this->columns[] = new Column($info, null);
            }
        }
        else
        {
            $this->columns[] = new Column($columns, null);
        }
    }


    /**
    * Return an array with columns.
    *
    * @return array with columns.
    */
    function getColumns()
    {
        return $this->columns;
    }


    /**
    * Return an Array with all column names.
    *
    * @return an Array with all column names.
    */
    function getColumnNames()
    {
        $columns = $this->getColumns();

        if ($columns)
        {
            foreach ($columns as $line => $info)
            {
                $result[] = $columns[$line]->getName();
            }
        }

        return $result;
    }


    /**
    * Return a needed column passing it name;
    *
    * @param $name the name of the column.
    *
    * @return the colum, if find, or false, if not.
    */
    function getColumn($name)
    {
        $columns = $this->getColumns();

        if (is_array($columns))
        {
            foreach($columns as $line => $info)
            {
                if ($info->getName()== $name);
                {
                    return $info;
                }
            }
            return false;
        }
        return false;
    }


    /**
    * Reset/Clean/Delete all columns.
    *
    */
    function clearColumns()
    {
        unset($this->columns);
    }

    /************** WHERE **************/

    private function getWhereToSql()
    {
        if ($this->where)
        {
            $where = $this->where;

            $result .= ' '.WHERE.' ' ;

            if (is_array($where))
            {
                foreach($where as $line => $info)
                {
                    if ($line==0)
                    {
                        $result.= $info->toSql(false);
                    }
                    else
                    {
                        $result.= $info->toSql(true);
                    }
                }
            }
        }

        return $result;
    }

    function addWhere($where, $quoted=null, $type=ANDD)
    {
        if (is_object($where))
        {
            //TODO verify type of Object
            $this->where[] = $where;
        }
        else
        {
            $this->where[] = new where($where, $quoted, $type);
        }
    }



    function getWhere()
    {
        return $this->where;
    }


    function clearWhere()
    {
        unset($this->where);
    }


    /************** LIMIT **************/

    function setlimit($limitBegin, $limitEnd)
    {
        $this->limitBegin   = $limitBegin;
        $this->limitEnd     = $limitEnd;
    }

    function setLimitBegin($limitBegin)
    {
        $this->limitBegin   = $limitBegin;
    }

    function setLimitEnd($limitEnd)
    {
        $this->limitEnd   = $limitEnd;
    }

    function getLimitString()
    {
        return $this->getLimitToSqL();
    }

    function clearLimit()
    {
        $this->limit=null;
    }

    function getLimitToSqL()
    {
        $limitBegin = $this->limitBegin;
        $limitEnd   = $this->limitEnd;

        if ($limitBegin && $limitEnd)
        {
            return LIMIT.' '. $limitBegin .','.$limitEnd;
        }
        else if ($limitBegin)
        {
            return LIMIT . ' '. $limitBegin;
        }
        else if ($limitEnd)
        {
            return LIMIT . ' ' . $limitEnd;
        }
        else
        {
            return '';
        }
    }


    /***************** ORDER BY *************/

    function setOrderBy($orderBy)
    {
        $this->orderBy= $orderBy;
    }

    function clearOrderBy()
    {
        unset( $this->orderBy );
    }

    function getOrderByToSql()
    {
        if ($this->orderBy)
        {
            $orderBy = $this->orderBy;
            return ORDERBY." $orderBy";
        }
        else
        {
            return "";
        }
    }



    function resultToArray($result)
    {
        foreach ($result as $line => $info)
        {
            foreach ($info as $item => $data)
            {

                if (!is_integer($item))
                {
                    $row[$item] = $data;
                }
            }
                $return[] = $row;
        }
        return $return;
    }


    public function autoInsert($data)
    {
        $sql = $this->insert($data);
        return $result = $this->db->exec( $sql );
    }

    public function autoRemove($data)
    {
        $this->clear();

        if (is_array($data))
        {
            foreach ($data as $line => $info)
            {
                $this->addWhere("$line=", $info);
            }
        }

        return $this->db->query( $this->delete() );

    }

    public function autoSelect($data=null)
    {
        $this->clearWhere();

        if (is_array($data))
        {
            foreach ($data as $line => $info)
            {
                $this->addWhere("$line=", $info);
            }
        }

        $sql = $this->select();
        $result = $this->db->query($sql);
        return  $this->resultToArray($result);
    }


}


/**
* WHERE
* A simple class to manage where clausules.
*
*
*
*/
class where
{
    private $where;
    private $quoted;
    private $type;

    /**
    * Construtor a where Clausule.
    *
    * @param $type the type of where, common values are 'AND', 'OR'
    * @param $where the where string;
    *
    */
    function where($where, $quoted = '',  $type = ANDD)
    {
        $this->where = $where;
        $this->quoted= $quoted;
        $this->type  = $type;
    }

    /**
    * Return the where object.
    *
    */
    function getWhere()
    {
        return $this->where;
    }

    /**
    * Defines the where
    *
    * @param $where the where clausule
    */
    function setWhere($where)
    {
        $this->where = $where;
    }


    /**
    * Return the type of this where.
    *
    */
    function getType()
    {
        return $this->type;
    }


    /**
    * Defines the where type
    *
    * @param $type the type of where clausule
    */
    function setType($type)
    {
        $this->type = $type;
    }

    /**
    * Define the quoted item of this where
    *
    */
    function setQuoted($quoted)
    {
        $this->quoted=$quoted;
    }


    /**
    * Return the quoted part of where.
    *
    */
    function getQuoted()
    {
        return $this->quoted;
    }

    /**
    * Return the where to sql string.
    *
    */
    function toSql($typeOn=false)
    {
        $where = $this->where;
        $type  = $this->type;
        $quoted= $this->quoted;

        if ($typeOn == true)
        {
            if ($quoted)
            {
                return " $type $where '$quoted'";
            }
            else
            {
                return " $type $where";
            }
        }
        else
        {
            if ($quoted)
            {
                return "$where '$quoted'";
            }
            else
            {
                return "$where";
            }
        }
    }

}




?>
